﻿using MuXue.Zyiz.Template.Common;
using MuXue.Zyiz.Template.Domain.IRepository;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;


namespace MuXue.Zyiz.Template.Infrastructure.Repository
{
    public class BaseRepository<T> : IRepository<T> where T : class
    {
        public readonly IDbContext _dbContext;
        UnitOfWork _UnitOfWork;

        public BaseRepository(IDbContext dbContext)
        {
            _dbContext = dbContext;
            _UnitOfWork = new UnitOfWork(_dbContext);
        }

        #region  新增

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual T AddEntity(T entity, bool IsCommit = true)
        {
            var rr = _dbContext.Set<T>().Add(entity);
            if (IsCommit)
            {
                int vv = _UnitOfWork.Commit();
            }
            return rr.Entity;
        }

        /// <summary>
        /// 异步新增
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual async Task<T> AddEntityAsync(T entity, bool IsCommit = true)
        {
            var rr = await _dbContext.Set<T>().AddAsync(entity);
            if (IsCommit)
            {
                await _UnitOfWork.CommitAsync();
            }
            return rr.Entity;
        }

        /// <summary>
        /// 新增实体列表
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual int AddRange(IEnumerable<T> entity, bool IsCommit = true)
        {
            int rest = 0;
            _dbContext.Set<T>().AddRange(entity);
            if (IsCommit)
            {
                rest = _UnitOfWork.Commit();
            }

            return rest;
        }

        /// <summary>
        /// 异步新增实体列表
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual async Task AddRangeAsync(IEnumerable<T> entity, bool IsCommit = true)
        {
            await _dbContext.Set<T>().AddRangeAsync(entity);
            if (IsCommit)
            {
                await _UnitOfWork.CommitAsync();
            }
        }

        #endregion

        #region 删除
        /// <summary>
        /// 删除实体
        /// </summary>
        /// <param name="where">实体列表</param>
        /// <returns></returns>
        public virtual int DelEntity(Expression<Func<T, bool>> where, bool IsCommit = true)
        {
            int result = 0;
            IEnumerable<T> entity = _dbContext.Set<T>().Where(where);
            _dbContext.Set<T>().RemoveRange(entity);
            if (IsCommit)
            {
                result = _UnitOfWork.Commit();
            }

            return result;
        }

        /// <summary>
        /// 删除实体
        /// </summary>
        /// <param name="where">实体列表</param>
        /// <returns></returns>
        public virtual async Task<int> DelEntityAsync(Expression<Func<T, bool>> where, bool IsCommit = true)
        {
            int result = 0;
            IEnumerable<T> entity = _dbContext.Set<T>().Where(where);
            _dbContext.Set<T>().RemoveRange(entity);
            if (IsCommit)
            {
                result =await  _UnitOfWork.CommitAsync();
            }

            return result;
        }

        #endregion

        #region 修改

        /// <summary>
        /// 修改实体
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual int UpdateEntity(T entity, bool IsCommit = true)
        {
            int result = 0;
            _dbContext.Entry<T>(entity).State = EntityState.Modified;
            if (IsCommit)
            {
                result = _UnitOfWork.Commit();
            }

            return result;
        }

        /// <summary>
        /// 修改实体
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="IsCommit"></param>
        /// <returns></returns>
        public virtual async Task<int> UpdateEntityAsync(T entity, bool IsCommit = true)
        {
            int result = 0;
            _dbContext.Entry<T>(entity).State = EntityState.Modified;
            if (IsCommit)
            {
                result =await  _UnitOfWork.CommitAsync();
            }

            return result;
        }

        /// <summary>
        /// 更新部分字段
        /// </summary>
        public virtual int UpdateEntity(T entity, Expression<Func<T, object>>[] updatedProperties, bool IsCommit = true)
        {
            int result = 0;
            _dbContext.Set<T>().Attach(entity);
            if (updatedProperties.Any())
            {
                foreach (var property in updatedProperties)
                {
                    _dbContext.Entry<T>(entity).Property(property).IsModified = true;
                }
            }
            if (IsCommit)
            {
                result = _UnitOfWork.Commit();
            }

            return result;
        }
        /// <summary>
        /// 更新部分字段-孟老师
        /// </summary>
        public virtual async Task<int> UpdateEntityAsync(T entity, Expression<Func<T, object>>[] updatedProperties, bool IsCommit = true)
        {
            int result = 0;
            _dbContext.Set<T>().Attach(entity);
            if (updatedProperties.Any())
            {
                foreach (var property in updatedProperties)
                {
                    _dbContext.Entry<T>(entity).Property(property).IsModified = true;
                }
            }
            if (IsCommit)
            {
                result= await _UnitOfWork.CommitAsync();
            }
            return result;
        }


        /// <summary>
        /// 批量修改实体
        /// </summary>
        /// <param name="where"></param>
        /// <param name="entity"></param>
        public virtual int UpdateEntityRange(IEnumerable<T> entity, bool IsCommit = true)
        {
            int result = 0;

            _dbContext.Set<T>().UpdateRange(entity);
            if (IsCommit)
            {
                result = _UnitOfWork.Commit();
            }

            return result;
        }

        /// <summary>
        /// 批量修改实体
        /// </summary>
        /// <param name="where"></param>
        /// <param name="entity"></param>
        public virtual async Task<int> UpdateEntityRangeAsync(IEnumerable<T> entity, bool IsCommit = true)
        {
            int result = 0; 
            _dbContext.Set<T>().UpdateRange(entity);
            if (IsCommit)
            {
                result =await  _UnitOfWork.CommitAsync();
            } 
            return result;
        }


        #endregion

        #region 查询

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns></returns>
        public List<T> LoadEntityAll()
        {
            return _dbContext.Set<T>().AsNoTracking().ToList();
        }

        /// <summary>
        /// 异步查询所有
        /// </summary>
        /// <returns></returns>
        public async Task<List<T>> LoadEntityAllAsync()
        {
            return await _dbContext.Set<T>().AsNoTracking().ToListAsync();
        }

        /// <summary>
        /// 根据条件查询实体列表
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public IEnumerable<T> WhereLoadEntityEnumerable(Expression<Func<T, bool>> where)
        {
            return _dbContext.Set<T>().Where(where);
        }

        /// <summary>
        /// 根据条件查询实体列表不跟踪
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public List<T> WhereLoadEntityListAsNoTracking(Expression<Func<T, bool>> where)
        {
            return _dbContext.Set<T>().AsNoTracking().Where(where).ToList();
        }

        /// <summary>
        /// 异步根据条件查询实体列表不跟踪
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public async Task<List<T>> WhereLoadEntityListAsNoTrackingAsync(Expression<Func<T, bool>> where)
        {
            return await _dbContext.Set<T>().AsNoTracking().Where(where).ToListAsync();
        }

        /// <summary>
        /// 根据条件查询实体
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public T WhereLoadEntity(Expression<Func<T, bool>> where)
        {
            return _dbContext.Set<T>().FirstOrDefault(where);
        }

        /// <summary>
        /// 根据条件异步查询实体
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public async Task<T> WhereLoadEntityAsync(Expression<Func<T, bool>> where)
        {
            return await _dbContext.Set<T>().FirstOrDefaultAsync(where);
        }


        /// <summary>
        /// 根据条件查询实体不跟踪
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public T WhereLoadEntityAsNoTracking(Expression<Func<T, bool>> where)
        {
            return _dbContext.Set<T>().AsNoTracking().FirstOrDefault(where);
        }

        /// <summary>
        /// 异步根据条件异步查询实体不跟踪
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public async Task<T> WhereLoadEntityAsNoTrackingAsync(Expression<Func<T, bool>> where)
        {
            return await _dbContext.Set<T>().AsNoTracking().FirstOrDefaultAsync(where);
        }

        /// <summary>
        /// 获取最大的一条数据
        /// </summary>
        /// <param name="order"></param>
        /// <returns></returns>
        public T LoadEntityMaxSort(Expression<Func<T, int>> order)
        {
            return _dbContext.Set<T>().OrderByDescending(order).FirstOrDefault();
        }

        /// <summary>
        /// 异步获取最大的一条数据
        /// </summary>
        /// <param name="order"></param>
        /// <returns></returns>
        public async Task<T> LoadEntityMaxSortAsync(Expression<Func<T, int>> order)
        {
            return await _dbContext.Set<T>().OrderByDescending(order).FirstOrDefaultAsync();
        }

        /// <summary>
        /// Any 查找数据判断数据是否存在
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public bool AnyEntity(Expression<Func<T, bool>> where)
        {
            return _dbContext.Set<T>().Any(where);
        }

        /// <summary>
        /// Any 查找数据判断数据是否存在
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public async Task<bool> AnyEntityAsync(Expression<Func<T, bool>> where)
        {
            return await _dbContext.Set<T>().AnyAsync(where);
        }

        #endregion

        #region 分页排序查询
        /// <summary>
        /// 加载自己定义排序分页实体列表
        /// </summary>
        /// <param name="where"></param>
        /// <param name="orderby"></param>
        /// <param name="asc">asc/desc</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public virtual IEnumerable<T> LoadEntityEnumerable(Expression<Func<T, bool>> where, Expression<Func<T, string>> orderby, string asc, int pageIndex, int pageSize)
        {
            --pageIndex;

            if (asc.Equals(nameof(asc)))
                return (IEnumerable<T>)_dbContext.Set<T>().Where<T>(where).OrderBy<T, string>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize);
            return (IEnumerable<T>)_dbContext.Set<T>().Where<T>(where).OrderByDescending<T, string>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking();
        }
        public virtual IEnumerable<T> LoadEntityEnumerable(Expression<Func<T, bool>> where, Expression<Func<T, int?>> orderby, string asc, int pageIndex, int pageSize)
        {
            --pageIndex;

            if (asc.Equals(nameof(asc)))
                return (IEnumerable<T>)_dbContext.Set<T>().Where<T>(where).OrderBy<T, int?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize);
            return (IEnumerable<T>)_dbContext.Set<T>().Where<T>(where).OrderByDescending<T, int?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking();

        }
        public virtual IEnumerable<T> LoadEntityEnumerable(Expression<Func<T, bool>> where, Expression<Func<T, DateTime?>> orderby, string asc, int pageIndex, int pageSize)
        {
            --pageIndex;

            if (asc.Equals(nameof(asc)))
                return (IEnumerable<T>)_dbContext.Set<T>().Where<T>(where).OrderBy<T, DateTime?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize);
            return (IEnumerable<T>)_dbContext.Set<T>().Where<T>(where).OrderByDescending<T, DateTime?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking();

        }
        public virtual IEnumerable<T> LoadEntityEnumerable(Expression<Func<T, bool>> where, Expression<Func<T, decimal?>> orderby, string asc, int pageIndex, int pageSize)
        {
            --pageIndex;

            if (asc.Equals(nameof(asc)))
                return (IEnumerable<T>)_dbContext.Set<T>().Where<T>(where).OrderBy<T, Decimal?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize);
            return (IEnumerable<T>)_dbContext.Set<T>().Where<T>(where).OrderByDescending<T, Decimal?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking();

        }
        public virtual IEnumerable<T> LoadEntityEnumerable(Expression<Func<T, bool>> where, Expression<Func<T, bool?>> orderby, string asc, int pageIndex, int pageSize)
        {
            --pageIndex;

            if (asc.Equals(nameof(asc)))
                return (IEnumerable<T>)_dbContext.Set<T>().Where<T>(where).OrderBy<T, bool?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize);
            return (IEnumerable<T>)_dbContext.Set<T>().Where<T>(where).OrderByDescending<T, bool?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking();

        }
        /// <summary>
        /// 加载自己定义排序分页实体列表
        /// </summary>
        /// <param name="where"></param>
        /// <param name="orderby"></param>
        /// <param name="asc">asc/desc</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public virtual async Task<List<T>> LoadEntityListAsync(Expression<Func<T, bool>> where, Expression<Func<T, string>> orderby, string asc, int pageIndex, int pageSize)
        {
            --pageIndex;

            if (asc.Equals(nameof(asc)))
                return await _dbContext.Set<T>().Where<T>(where).OrderBy<T, string>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking().ToListAsync();
            return await _dbContext.Set<T>().Where<T>(where).OrderByDescending<T, string>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking().ToListAsync();

        }
        public virtual async Task<List<T>> LoadEntityListAsync(Expression<Func<T, bool>> where, Expression<Func<T, int?>> orderby, string asc, int pageIndex, int pageSize)
        {
            --pageIndex;

            if (asc.Equals(nameof(asc)))
                return await _dbContext.Set<T>().Where<T>(where).OrderBy<T, int?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking().ToListAsync();
            return await _dbContext.Set<T>().Where<T>(where).OrderByDescending<T, int?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking().ToListAsync();

        }
        public virtual async Task<List<T>> LoadEntityListAsync(Expression<Func<T, bool>> where, Expression<Func<T, DateTime?>> orderby, string asc, int pageIndex, int pageSize)
        {
            --pageIndex;

            if (asc.Equals(nameof(asc)))
                return await _dbContext.Set<T>().Where<T>(where).OrderBy<T, DateTime?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking().ToListAsync();
            return await _dbContext.Set<T>().Where<T>(where).OrderByDescending<T, DateTime?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking().ToListAsync();

        }
        public virtual async Task<List<T>> LoadEntityListAsync(Expression<Func<T, bool>> where, Expression<Func<T, decimal?>> orderby, string asc, int pageIndex, int pageSize)
        {
            --pageIndex;

            if (asc.Equals(nameof(asc)))
                return await _dbContext.Set<T>().Where<T>(where).OrderBy<T, Decimal?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking().ToListAsync();
            return await _dbContext.Set<T>().Where<T>(where).OrderByDescending<T, Decimal?>(orderby).Skip<T>(pageIndex * pageSize).Take<T>(pageSize).AsNoTracking().ToListAsync();

        }

        #endregion

        #region 求平均，求总计
        public virtual int? GetSum(Expression<Func<T, bool>> where, Expression<Func<T, int?>> sum)
        {
            return _dbContext.Set<T>().Where<T>(where).Sum<T>(sum);
        }
        public virtual double? GetSum(Expression<Func<T, bool>> where, Expression<Func<T, double?>> sum)
        {
            return _dbContext.Set<T>().Where<T>(where).Sum<T>(sum);
        }
        public virtual float? GetSum(Expression<Func<T, bool>> where, Expression<Func<T, float?>> sum)
        {
            return _dbContext.Set<T>().Where<T>(where).Sum<T>(sum);
        }
        public virtual decimal? GetSum(Expression<Func<T, bool>> where, Expression<Func<T, decimal?>> sum)
        {
            return _dbContext.Set<T>().Where<T>(where).Sum<T>(sum);
        }
        public virtual double? GetAvg(Expression<Func<T, bool>> where, Expression<Func<T, int?>> avg)
        {
            return _dbContext.Set<T>().Where<T>(where).Average<T>(avg);
        }
        public virtual double? GetAvg(Expression<Func<T, bool>> where, Expression<Func<T, double?>> avg)
        {
            return _dbContext.Set<T>().Where<T>(where).Average<T>(avg);
        }
        public virtual float? GetAvg(Expression<Func<T, bool>> where, Expression<Func<T, float?>> avg)
        {
            return _dbContext.Set<T>().Where<T>(where).Average<T>(avg);
        }
        public virtual decimal? GetAvg(Expression<Func<T, bool>> where, Expression<Func<T, decimal?>> avg)
        {
            return _dbContext.Set<T>().Where<T>(where).Average<T>(avg);
        }


        #endregion

        #region 查最大
        /// <summary>
        /// 查最大
        /// </summary>
        /// <param name="max"></param>
        /// <returns></returns>
        public virtual int? GetMax(Expression<Func<T, int?>> max)
        {
            return _dbContext.Set<T>().Max<T, int?>(max);
        }
        /// <summary>
        /// 查最大
        /// </summary>
        /// <param name="max"></param>
        /// <returns></returns>
        public virtual double? GetMax(Expression<Func<T, double?>> max)
        {
            return _dbContext.Set<T>().Max<T, double?>(max);
        }
        /// <summary>
        /// 查最大
        /// </summary>
        /// <param name="max"></param>
        /// <returns></returns>
        public virtual decimal? GetMax(Expression<Func<T, decimal?>> max)
        {
            return _dbContext.Set<T>().Max<T, decimal?>(max);
        }
        /// <summary>
        /// 查最大
        /// </summary>
        /// <param name="max"></param>
        /// <returns></returns>
        public virtual DateTime? GetMax(Expression<Func<T, DateTime?>> max)
        {
            return _dbContext.Set<T>().Max<T, DateTime?>(max);
        }
        #endregion

        #region 查询实体数量
        /// <summary>
        /// 查询实体数量
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public virtual int GetEntitiesCount(Expression<Func<T, bool>> where)
        {
            return _dbContext.Set<T>().Count<T>(where);
        }

        /// <summary>
        /// 异步查询实体数量
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public virtual async Task<int> GetEntitiesCountAsync(Expression<Func<T, bool>> where)
        {
            return await _dbContext.Set<T>().CountAsync<T>(where);
        }
        #endregion

        #region 执行SQL

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual int ExecuteSql(string sql, object[] parameters = null)
        {
            return _dbContext.Database.ExecuteSqlCommand(sql, parameters);
        }

        public virtual async Task<int> ExecuteSqlAsync(string sql, object[] parameters = null)
        {
            return await _dbContext.Database.ExecuteSqlCommandAsync(sql, parameters);
        }
        /// <summary>
        /// 执行SQL查询-返回List
        /// </summary>
        public virtual IEnumerable<Entity> QueryBySql<Entity>(string sql, object[] parameters = null)
        {
            DbConnection connection = _dbContext.Database.GetDbConnection();
            DbCommand cmd = connection.CreateCommand();
            connection.Open();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
            }
            DataTable dt = new DataTable();
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
                cmd.Parameters.Clear();
            }
            connection.Close();
            return ConvertHelper.DataTableToIList<Entity>(dt).ToList();
        }
        /// <summary>
        /// 使用sql脚本查询实体列表（分页）--带参数
        /// </summary>
        /// <returns></returns>
        public virtual IEnumerable<Entity> QueryBySqlRowNum<Entity>(string sqlString, string selectFields, string sortString, int pageSize, int pageIndex, ref int recordCount, object[] parameters = null)
        {
            if (recordCount < 0)
            {
                string str = "select count(*) from  ( {0} )  as  a ";
                recordCount = Convert.ToInt32(this.QueryTBySql<int>(string.Format(str, sqlString), parameters));
            }
            string str2 = " order by (select 1)";
            if ((sortString != "") && (sortString != null))
            {
                str2 = string.Format("ORDER BY {0}", sortString);
            }
            string str3 = "";
            str3 = string.Format("select * from ( select ROW_NUMBER() OVER(" + str2 + ") AS rownum,* from ( select {0} from ({1}) as a ) as b) as c where rownum between {2} and {3}", new object[] { selectFields, sqlString, (pageIndex * pageSize) + 1, (pageIndex + 1) * pageSize });
            return this.QueryBySql<Entity>(str3, parameters);
        }

        /// <summary>
        /// 执行sql语句，获取一个对象实体
        /// </summary>
        /// <typeparam name="Entity"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual Entity QueryEntityBySql<Entity>(string sql, object[] parameters = null)
        {
            DbConnection connection = _dbContext.Database.GetDbConnection();
            DbCommand cmd = connection.CreateCommand();
            connection.Open();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
            }
            DataTable dt = new DataTable();
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
                cmd.Parameters.Clear();
            }
            connection.Close();
            if (dt != null && dt.Rows.Count > 0)
            {

                return ConvertHelper.DataTableToEntity<Entity>(dt);
            }
            else
            {
                return default(Entity);
            }

        }

        #endregion
        /// <summary>
        /// 执行sql语句，获取一个string 或者其他单个类型的对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual T QueryTBySql<T>(string sql, object[] parameters = null)
        {
            DbConnection connection = _dbContext.Database.GetDbConnection();
            DbCommand cmd = connection.CreateCommand();
            connection.Open();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
            }
            DataTable dt = new DataTable();
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
                cmd.Parameters.Clear();
            }
            connection.Close();
            if (dt != null && dt.Rows.Count > 0)
            {
                var rr = dt.Rows[0][0];
                return (T)rr;

            }
            else
            {
                return default(T);
            }

        }
    }
}
